# -*- coding: utf-8 -*-
"""
Created on Thu Nov 16 12:23:46 2017

@author: leshuangjiang
"""

import os
import sqlite3 as sql
import xlwings as xw
#import time
import pandas as pd

PathSumAcc = (r'\\10.90.18.92\森马事业部\供应链管理部\供应链管理部(部门内)\面辅料价格\18Q4\梭织\18Q4辅料ODM大表.xlsx')
PathSumMat = (r'\\10.90.18.92\森马事业部\供应链管理部\供应链管理部(部门内)\面辅料价格\18Q4\梭织\18Q4面料ODM大表.xlsx')

def drop_table():
    cur.execute('''drop table acce ''')
    cur.execute('''drop table mate ''')
    conn.commit()
    return '表格已经被删除'

def create_table():
    ''''''
    create_acc = ('create table acce (款号 text, 编号 char(50), 物料'
                    '名称 text, 颜色 text, 规格 text, 用量 char(30),'
                    '价格 decimal(3,3), 备注 text);')
    create_mat = '''create table mate (款号 text, 编号 char(50), 物料名称 text, 
                    克重 text, 成分 text, 规格 text, 有效门幅 text, 用量 char(30), 
                    价格 decimal(3,3), 备注 text);'''   
    cur.execute(create_acc)
    cur.execute(create_mat)
    conn.commit()
#    
def acc_data_get(book):
    ''''''
    sheet = book.sheets(1)
    col = list(sheet.Range('b1:ab1').value[0])
    loc_1 = ''.join(['b','2:', 'b', str(500000)])
    rg =  [i[0] for i in list(sheet.Range(loc_1).value) if i[0]!=None]
#    with open(r'D:\HaveRows.txt', 'w') as f:
#        f.write(str(len(rg)+num))
    loc_2 = ''.join(['b','2:', 'ab', str(len(rg))])
    all_data = [list(i) for i in list(sheet.Range(loc_2).value)]
    book.close
    df = pd.DataFrame(all_data, columns=col)
    need_df = df.loc[:,['款号', '编号', '物料名称', '颜色', '规格', '用量', '价格', '备注']].copy()
    need_df['款号'] = need_df['款号'].astype('str').str[:-2].copy()
    need_df['编号'] = need_df['编号'].apply(lambda x: str(int(x)) if isinstance(x, float) else x )
#    need_df['编号'] = need_df['编号'].apply(lambda x: x.split('.')[0])
#    need_df['编号'] = need_df['编号'].astype('str')  
    need_df.replace([None], '', inplace=True)
    return need_df.values

def mat_data_get(book):
    ''''''
    sheet = book.sheets(1)
    col = list(sheet.Range('c1:ac1').value[0])
    loc_1 = ''.join(['c', '2:', 'c', str(100000)])
    rg =  [i for i in list(sheet.Range(loc_1).value) if i[0]!=None]
#    with open(r'D:\HaveRowsMat.txt', 'w') as f:
#        f.write(str(len(rg)+num))
    loc_2 = ''.join(['c', '2:', 'ac', str(len(rg))])
    all_data = [list(i) for i in list(sheet.Range(loc_2).value)]
    book.close
    df = pd.DataFrame(all_data, columns=col)
    need_df_mat = df.loc[:,['款号', '编号', '物料名称', '克重(g/m2)', '成份', '规格', '有效门幅（CM)', '单位', '核价(元/米）', '备注']].copy()
    need_df_mat['款号'] = need_df_mat['款号'].astype('str').str[:-2]
    need_df_mat['编号'] = need_df_mat['编号'].apply(lambda x: str(int(x)) if isinstance(x, float) else x)
#    need_df_mat['编号'] = ['编号'].apply(lambda x: x.split('.')[0])
    need_df_mat.replace([None], '', inplace=True)
    return need_df_mat.values
   
def data_trans():
    ''''''
#    with open(r'D:\HaveRows.txt') as f:
#        num = int(f.read())+1
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False
    xb_acc = app.books.api.open(PathSumAcc, 0, True, None, None, None, True)
    xb_mat = app.books.api.open(PathSumMat, 0, True, None, None, None, True)
#    xb_mianliao = app.books.open(r'D:\文档\表格\IE科\面料辅料表\面辅料\副本18Q3梭织面料---ODM --11月10日.xlsx')
    all_d_acc = acc_data_get(xb_acc)
    all_d_mat = mat_data_get(xb_mat)
    app.quit()
#    all_d.extend(DaTa(xb_mianliao))
    return all_d_mat, all_d_acc

d_mat, acc_data = data_trans()
def mat_data_in():
    '''写入数据到数据库'''
    for i in d_mat:
        insert = ('''insert into mate ('款号', '编号', '物料名称', '克重', '成分', '规格', '有效门幅', 
        '用量', '价格', '备注') values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''')
        para = ['' if x is None else x for x in i]
#        print(para)
        cur.execute(insert, para)
    conn.commit()
    return '所有数据已经转换完成'
    
def acc_data_in():
    for i in acc_data:
        insert = ('''insert into acce ('款号', '编号','物料名称', '颜色', '规格', 
        '用量', '价格', '备注') values (?, ?, ?, ?, ?, ?, ?, ?)''')
        para = ['' if x is None else x for x in i]
#        print(para)
        cur.execute(insert, para)
    conn.commit()
    return '所有数据已经转换完成'

if __name__ == '__main__':
    try:
        os.remove(r'D:\价格批导\MYSQL\18Q4梭织.db')
    except Exception as e:
        print(e)
        print('数据表已经存在')
        conn = sql.connect(r'D:\价格批导\MYSQL\18Q4梭织.db')
        cur = conn.cursor()
        create_table()
    print('开始写入数据')
    mat_data_in()
    acc_data_in()
    conn.close()
#    os.system('taskkill /f /im excel.exe')
#    os.system('taskkill /PID app.pid')
    print('所有的数据都已经写入')

